﻿
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [vSkillsCourseStudentOnlyAnswer_AllCourses]
AS
--AMG 23/03/2024
--Only the Avg of all of the Course Student answers to the questions are returned - tabular
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsCourseStudentAnswer.OGP_StudentID,

		CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
			CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(SkillsAnswer.[Value]) AS CountofAnswers
	FROM 
		SkillsCourseStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN Course ON SkillsCourseStudentAnswer.CourseID = Course.ID
	GROUP BY
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.ID,
		SkillsPeriod.DefaultDescription,
		SkillsCourseStudentAnswer.OGP_StudentID


GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot]
AS
--AMG 25/03/2024
--Only the Avg of ALL of the Course Student answers to the questions are returned - but Pivoted

SELECT 
	AcademicYearID, OGP_StudentID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		FROM vSkillsCourseStudentOnlyAnswer_AllCourses
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable




GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE VIEW [vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot]
AS
--AMG 25/03/2024
--The Avg of the Student answers to the student questions - Pivoted
--By Question
SELECT 
	AcademicYearID, OGP_StudentID, SkillsQuestionID,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		--SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		--FROM vSkillsStudentOnlyAnswer

		SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsStudentAnswer.OGP_StudentID,
		SkillsStudentAnswer.SkillsQuestionID,
		SkillsAnswer.[Value] AS AnswerValue
		FROM 
		SkillsStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 


	) AS sourcetable
	PIVOT  
	(  
	  MAX(AnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable
	



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_ByCourse_CourseQu]
@CourseID INT
AS

-- =============================================
-- Author:		Andrew Genner
-- Create date: 15/03/2024
-- Descripton:	Shows Course Questions that have been answered and shows the average score per period for each.
-- =============================================

--exec sp_WebPlus_Skills_Reporting_ByCourse_CourseQu 2790

--Get the basic data into a table
SELECT
	SkillsPeriod.DefaultDescription AS [Period],
	SkillsCourseQuestion.[Description] AS Question,
	CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
		CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
	END AS AvgAnswerValue,
	SkillsCourseQuestion.OrderBy
INTO
	#TempAnswerAvgPerQuPerPeriod
FROM 
	SkillsCourseStudentAnswer 
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsCourseQuestion ON SkillsCourseStudentAnswer.SkillsCourseQuestionID = SkillsCourseQuestion.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
WHERE 
	SkillsCourseStudentAnswer.CourseID = @CourseID
GROUP BY 
	SkillsPeriod.DefaultDescription,
	SkillsCourseQuestion.[Description],
	SkillsCourseQuestion.OrderBy

UNION SELECT
	SkillsPeriod.DefaultDescription AS [Period],
	'Overall Average' AS Question,
	CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
		CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
	END AS AvgAnswerValue,
	999999 As OrderBy

FROM 
	SkillsCourseStudentAnswer 
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsCourseQuestion ON SkillsCourseStudentAnswer.SkillsCourseQuestionID = SkillsCourseQuestion.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
WHERE 
	SkillsCourseStudentAnswer.CourseID = @CourseID

GROUP BY 
	SkillsPeriod.DefaultDescription


SELECT 
	Question,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT Question, OrderBy, [Period], AvgAnswerValue
		FROM #TempAnswerAvgPerQuPerPeriod
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR [Period] IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable

ORDER BY
	OrderBy ASC

/*

--DECLARE @AcademicYearID AS VARCHAR(5)
--SELECT @AcademicYearID = (SELECT AcademicYearID FROM Course WHERE ID = @CourseID)

--This is a dynamic pivot because we don't know how many Columns (Periods) we will have...

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ '[' + [Period] + ']'
FROM (SELECT DefaultDescription AS [Period] FROM SkillsPeriod WHERE AcademicYearID = @AcademicYearID AND LEN(SkillsPeriodDescription) > 0) AS [Period]


--Get SELECT distinct values of the PIVOT Column
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ '' + '[' + [Period] + ']' + ' AS '
+ '[' + [Period] + ']'
FROM (SELECT DefaultDescription AS [Period] FROM SkillsPeriod WHERE AcademicYearID = @AcademicYearID AND LEN(SkillsPeriodDescription) > 0) AS [Period]


--Prepare the PIVOT using dynamic query
SET @DynamicPivotQuery = 
N'SELECT Question, ' + @PivotSelectColumnNames + '
FROM #TempAnswerAvgPerQuPerPeriod
pivot(sum(AvgAnswerValue) for [Period] in (' + @PivotColumnNames + ')) as pvt'

--Execute the Dynamic Pivot Query
EXEC (@DynamicPivotQuery)


*/



GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_ByCourse_CrossCollegeQu]
@CourseID INT
AS

-- =============================================
-- Author:		Andrew Genner
-- Create date: 15/03/2024
-- Descripton:	Shows Cross-College Questions that have been answered and shows the average score per period for each.
-- =============================================

--exec sp_WebPlus_Skills_Reporting_ByCourse_CrossCollegeQu 2790
--Get the basic data into a table
SELECT
	SkillsPeriod.DefaultDescription AS [Period],
	SkillsQuestion.[Description] AS Question,
	CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
		CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
	END AS AvgAnswerValue,
	SkillsQuestion.OrderBy
INTO
	#TempAnswerAvgPerQuPerPeriod
FROM 
	SkillsStudentAnswer 
		INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsQuestion ON SkillsStudentAnswer.SkillsQuestionID = SkillsQuestion.ID
		INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN OGP_Enrolment ON OGP_Enrolment.OGP_StudentID = SkillsStudentAnswer.OGP_StudentID
WHERE 
	OGP_Enrolment.CourseID = @CourseID
	AND OGP_Enrolment.RecordStatus <> 'Obsolete'
GROUP BY 
	SkillsPeriod.DefaultDescription,
	SkillsQuestion.[Description],
	SkillsQuestion.OrderBy

UNION SELECT
	SkillsPeriod.DefaultDescription AS [Period],
	'Overall Average' AS Question,
	CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
		CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
	END AS AvgAnswerValue,
	999999 As OrderBy

FROM 
	SkillsStudentAnswer 
		INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsQuestion ON SkillsStudentAnswer.SkillsQuestionID = SkillsQuestion.ID
		INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN OGP_Enrolment ON OGP_Enrolment.OGP_StudentID = SkillsStudentAnswer.OGP_StudentID
WHERE 
	OGP_Enrolment.CourseID = @CourseID

GROUP BY 
	SkillsPeriod.DefaultDescription


SELECT 
	Question,
	[Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10]

FROM  
	(
		SELECT Question, OrderBy, [Period], AvgAnswerValue
		FROM #TempAnswerAvgPerQuPerPeriod
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR [Period] IN ([Period01], [Period02], [Period03], [Period04], [Period05], [Period06], [Period07], [Period08], [Period09], [Period10])
	) AS PivotTable

ORDER BY
	OrderBy ASC



GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [sp_WebPlus_Skills_Reporting_CrossCollege_ByStudent_ByQuestion] '23/24', 4

CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_CrossCollege_ByStudent_ByQuestion]
  @AcademicYearID   AS VARCHAR(5),
  @QuestionID AS INT
AS
--AMG 25/03/2024
--Only shows one row per student.
--Pass in a Question Parameter and see how it has been answered by each student in each period.

--DECLARE @AcademicYearID   AS VARCHAR(5)
--SELECT @AcademicYearID = '23/24'
----Now, (for Student Cross College) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN Period02 IS NULL THEN
			(CASE WHEN Period03 IS NULL THEN
				(CASE WHEN Period04 IS NULL THEN
					(CASE WHEN Period05 IS NULL THEN
						(CASE WHEN Period06 IS NULL THEN
							(CASE WHEN Period07 IS NULL THEN
								(CASE WHEN Period08 IS NULL THEN
									(CASE WHEN Period09 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period09 END)
								ELSE Period08 END)
							ELSE Period07 END) 
						ELSE Period06 END)
					ELSE Period05 END)
				ELSE Period04 END)
			ELSE Period03 END)
		ELSE Period02 END)
	ELSE Period01 END)

AS StartingPeriodValue,

	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)

AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsStudentOnlyAnswerAvg_ByQuestion_Latestsub
FROM vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot
WHERE AcademicYearID = @AcademicYearID
AND SkillsQuestionID = @QuestionID




--and (for Student Cross-College) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsStudentAvgByQuestion_Latest
FROM
	#TempvSkillsStudentOnlyAnswerAvg_ByQuestion_Latestsub




--For the Overall Student Enrolment Status (Cross College),
		
--The Enrolment OverallCompletionID should only be based on 'Imported' records.
--Firstly Load up a series of #Temptables with a count of the completion statuses ...
--It is important that we only take the 'Imported' records - we don't want to look at adhoc records here!
SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp1 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 1 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp2 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 2 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp3 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 3 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp4 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 4 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp5 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 5 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp6 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 6 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID



SELECT 
	OGP_Student.ID AS OGP_StudentID,
	(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
		(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
			(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
				(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
					(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
					99 --Other!
					END) -- Xfer
				END)
			END)
		END)
	END) AS StudentOverallCompletionID

INTO
	#TempOverallStudentCompletion
FROM
	OGP_Student
		INNER JOIN
		(
		SELECT 
			OGP_Student.ID,
			(SELECT CountCompletion FROM #TempStudent_Comp1 WHERE #TempStudent_Comp1.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_1,
			(SELECT CountCompletion FROM #TempStudent_Comp2 WHERE #TempStudent_Comp2.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_2,
			(SELECT CountCompletion FROM #TempStudent_Comp3 WHERE #TempStudent_Comp3.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_3,
			(SELECT CountCompletion FROM #TempStudent_Comp4 WHERE #TempStudent_Comp4.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_4,
			(SELECT CountCompletion FROM #TempStudent_Comp5 WHERE #TempStudent_Comp5.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_5,
			(SELECT CountCompletion FROM #TempStudent_Comp6 WHERE #TempStudent_Comp6.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_6
		FROM
			OGP_Student	
		WHERE
			OGP_Student.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

		ON t.ID = OGP_Student.ID
		 

	


SELECT
	(SELECT [Description] FROM SkillsQuestion WHERE ID = @QuestionID) AS SkillsQuestionDescription,
	OGP_Student.AcademicYearID, StudentRef, Forenames, Surname, 
	NULL AS CourseCode, NULL AS CourseTitle, --'Cross-College' AS OGP_TypeCode,
	(SELECT dbo.GetAgeOnDate(
	CAST((SELECT CAST(AcademicYearNo AS VARCHAR(4)) FROM AcademicYear WHERE AcademicYear.AcademicYearID = OGP_Student.AcademicYearID ) + '0831' AS DateTime),
	OGP_Student.DOB
	)) AS AgeOn31Aug,
	Gender, 
	(SELECT Ethnicity.Code + ' - ' + Ethnicity.[Definition] FROM Ethnicity WHERE Ethnicity.ID = OGP_Student.EthnicityID) AS Ethnicity, 
	DOB, 
	NULL AS StartDate, NULL AS PlannedEndDate, NULL AS ActualEndDate, NULL AS NotionalNVQLevel,
	#TempOverallStudentCompletion.StudentOverallCompletionID, 
	ISNULL((SELECT [Description]FROM Completion WHERE Completion.ID = #TempOverallStudentCompletion.StudentOverallCompletionID), 'n/a') AS CompletionStatus, NULL AS Grade,
	NULL AS TeachingGroupCode, NULL AS TeachingGroupTitle, NULL AS LearningAimRef, NULL AS LearningAimRefTitle, NULL AS CollegeStructure,
	UserDefinedString1 AS StudentUserDefinedString1, UserDefinedString2 AS StudentUserDefinedString2, UserDefinedString3 AS StudentUserDefinedString3, UserDefinedString4 AS StudentUserDefinedString4,
	UserDefinedString5 AS StudentUserDefinedString5, UserDefinedString6 AS StudentUserDefinedString6, UserDefinedString7 AS StudentUserDefinedString7, UserDefinedString8 AS StudentUserDefinedString8,
	--UserDefinedNumber, UserDefinedNumber2, UserDefinedDate, 
	(SELECT LLDDandHealthProblem.Code + ' - ' + LLDDandHealthProblem.[Definition] FROM LLDDandHealthProblem WHERE LLDDandHealthProblem.ID = OGP_Student.LLDDandHealthProblemID) AS LLDDandHealthProblem, 
	OGP_Student.FreeSchoolMeal,
	
	OGP_Student.ID AS OGP_StudentID,

	 vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period01 AS CrossCollPeriod01,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period02 AS CrossCollPeriod02,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period03 AS CrossCollPeriod03, vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period04 AS CrossCollPeriod04,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period05 AS CrossCollPeriod05,  
	 vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period06 AS CrossCollPeriod06,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period07 AS CrossCollPeriod07,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period08 AS CrossCollPeriod08, vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period09 AS CrossCollPeriod09,  vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.Period10 AS CrossCollPeriod10,
	#TempvSkillsStudentAvgByQuestion_Latest.Diff_Start_Latest AS CrossCollDifference,
	#TempvSkillsStudentAvgByQuestion_Latest.StartingPeriod AS CrossCollStartingPeriod,
	#TempvSkillsStudentAvgByQuestion_Latest.MostRecentPeriod AS CrossCollMostRecentPeriod

FROM 
	OGP_Student 
	LEFT JOIN #TempvSkillsStudentAvgByQuestion_Latest ON OGP_Student.ID =  #TempvSkillsStudentAvgByQuestion_Latest.OGP_StudentID
	LEFT JOIN vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot ON OGP_Student.ID = vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.OGP_StudentID AND vSkillsStudentOnlyAnswer_ByQuestion_AvgPivot.SkillsQuestionID = @QuestionID
	LEFT JOIN #TempOverallStudentCompletion ON OGP_Student.ID = #TempOverallStudentCompletion.OGP_StudentID

WHERE
	OGP_Student.AcademicYearID = @AcademicYearID
	AND OGP_Student.RecordStatus <> 'Obsolete'

GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [sp_WebPlus_Skills_Reporting_ByStudent] '23/24'

CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_ByStudent]
  @AcademicYearID   AS VARCHAR(5)
AS
--AMG 13/02/2024
--The DataSource for the main reporting page in OneGradePlus for Skills
--We need to calculate the starting point and the MostRecentPoint
--So if a student starts in period 3, that is their starting point - we can then calculate the difference between the start and end point.
--Shows the Course Answers for the Course and the Overall Student Answers.
--AMG 25/03/2024
--This now only shows one row per student.
--It shows the CrossCollege Avg, the Avg for ALL Courses and the Overall Avg for each period, the Start and End and Difference also.

--DECLARE @AcademicYearID   AS VARCHAR(5)
--SELECT @AcademicYearID = '23/24'
----Now, (for Student Cross College) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN Period02 IS NULL THEN
			(CASE WHEN Period03 IS NULL THEN
				(CASE WHEN Period04 IS NULL THEN
					(CASE WHEN Period05 IS NULL THEN
						(CASE WHEN Period06 IS NULL THEN
							(CASE WHEN Period07 IS NULL THEN
								(CASE WHEN Period08 IS NULL THEN
									(CASE WHEN Period09 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period09 END)
								ELSE Period08 END)
							ELSE Period07 END) 
						ELSE Period06 END)
					ELSE Period05 END)
				ELSE Period04 END)
			ELSE Period03 END)
		ELSE Period02 END)
	ELSE Period01 END)

AS StartingPeriodValue,

	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)

AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsStudentOnlyAnswerAvg_Latestsub
FROM vSkillsStudentOnlyAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID


--Now, (for ALL Courses) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
AcademicYearID,

	(CASE WHEN  Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE  Period10 END)
									ELSE  Period09 END)
								ELSE  Period08 END)
							ELSE  Period07 END) 
						ELSE  Period06 END)
					ELSE  Period05 END)
				ELSE  Period04 END)
			ELSE  Period03 END)
		ELSE  Period02 END)				
	ELSE  Period01 END)
 AS StartingValue,

	(CASE WHEN  Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN 
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod, 
 
  	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
 					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 10 END)
AS MostRecentPeriod
INTO #TempvSkillsCourseStudentOnlyAnswerAvg_AllCourses_Latestsub
FROM vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot
WHERE AcademicYearID = @AcademicYearID

--Now, (for Overall) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN Period02 IS NULL THEN
			(CASE WHEN Period03 IS NULL THEN
				(CASE WHEN Period04 IS NULL THEN
					(CASE WHEN Period05 IS NULL THEN
						(CASE WHEN Period06 IS NULL THEN
							(CASE WHEN Period07 IS NULL THEN
								(CASE WHEN Period08 IS NULL THEN
									(CASE WHEN Period09 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period09 END)
								ELSE Period08 END)
							ELSE Period07 END) 
						ELSE Period06 END)
					ELSE Period05 END)
				ELSE Period04 END)
			ELSE Period03 END)
		ELSE Period02 END)
	ELSE Period01 END)
AS StartingPeriodValue,

	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsOverallStudentAnswerAvg_Latestsub
FROM vSkillsOverallStudentAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID


--and (for Student Cross-College) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsStudentAvg_Latest
FROM
	#TempvSkillsStudentOnlyAnswerAvg_Latestsub

--and (for ALL Courses) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	AcademicYearID,
	StartingPeriod,
	MostRecentPeriod,
	(CASE WHEN StartingValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsAllCoursesAvg_Latest
FROM
	#TempvSkillsCourseStudentOnlyAnswerAvg_AllCourses_Latestsub

--and (for Overall) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsOverallAvg_Latest
FROM
	#TempvSkillsOverallStudentAnswerAvg_Latestsub


--For the Overall Student Enrolment Status (Cross College),
		
--The Enrolment OverallCompletionID should only be based on 'Imported' records.
--Firstly Load up a series of #Temptables with a count of the completion statuses ...
--It is important that we only take the 'Imported' records - we don't want to look at adhoc records here!
SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp1 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 1 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp2 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 2 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp3 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 3 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp4 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 4 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp5 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 5 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp6 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 6 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID



SELECT 
	OGP_Student.ID AS OGP_StudentID,
	(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
		(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
			(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
				(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
					(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
					99 --Other!
					END) -- Xfer
				END)
			END)
		END)
	END) AS StudentOverallCompletionID

INTO
	#TempOverallStudentCompletion
FROM
	OGP_Student
		INNER JOIN
		(
		SELECT 
			OGP_Student.ID,
			(SELECT CountCompletion FROM #TempStudent_Comp1 WHERE #TempStudent_Comp1.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_1,
			(SELECT CountCompletion FROM #TempStudent_Comp2 WHERE #TempStudent_Comp2.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_2,
			(SELECT CountCompletion FROM #TempStudent_Comp3 WHERE #TempStudent_Comp3.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_3,
			(SELECT CountCompletion FROM #TempStudent_Comp4 WHERE #TempStudent_Comp4.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_4,
			(SELECT CountCompletion FROM #TempStudent_Comp5 WHERE #TempStudent_Comp5.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_5,
			(SELECT CountCompletion FROM #TempStudent_Comp6 WHERE #TempStudent_Comp6.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_6
		FROM
			OGP_Student	
		WHERE
			OGP_Student.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

		ON t.ID = OGP_Student.ID
		 

	


SELECT

	OGP_Student.AcademicYearID, StudentRef, Forenames, Surname, 
	NULL AS CourseCode, NULL AS CourseTitle, --'Cross-College' AS OGP_TypeCode,
	(SELECT dbo.GetAgeOnDate(
	CAST((SELECT CAST(AcademicYearNo AS VARCHAR(4)) FROM AcademicYear WHERE AcademicYear.AcademicYearID = OGP_Student.AcademicYearID ) + '0831' AS DateTime),
	OGP_Student.DOB
	)) AS AgeOn31Aug,
	Gender, 
	(SELECT Ethnicity.Code + ' - ' + Ethnicity.[Definition] FROM Ethnicity WHERE Ethnicity.ID = OGP_Student.EthnicityID) AS Ethnicity, 
	DOB, 
	NULL AS StartDate, NULL AS PlannedEndDate, NULL AS ActualEndDate, NULL AS NotionalNVQLevel,
	#TempOverallStudentCompletion.StudentOverallCompletionID, 
	ISNULL((SELECT [Description]FROM Completion WHERE Completion.ID = #TempOverallStudentCompletion.StudentOverallCompletionID), 'n/a') AS CompletionStatus, NULL AS Grade,
	NULL AS TeachingGroupCode, NULL AS TeachingGroupTitle, NULL AS LearningAimRef, NULL AS LearningAimRefTitle, NULL AS CollegeStructure,
	UserDefinedString1 AS StudentUserDefinedString1, UserDefinedString2 AS StudentUserDefinedString2, UserDefinedString3 AS StudentUserDefinedString3, UserDefinedString4 AS StudentUserDefinedString4,
	UserDefinedString5 AS StudentUserDefinedString5, UserDefinedString6 AS StudentUserDefinedString6, UserDefinedString7 AS StudentUserDefinedString7, UserDefinedString8 AS StudentUserDefinedString8,
	--UserDefinedNumber, UserDefinedNumber2, UserDefinedDate, 

	(SELECT LLDDandHealthProblem.Code + ' - ' + LLDDandHealthProblem.[Definition] FROM LLDDandHealthProblem WHERE LLDDandHealthProblem.ID = OGP_Student.LLDDandHealthProblemID) AS LLDDandHealthProblem, 
	OGP_Student.FreeSchoolMeal,
	
	OGP_Student.ID AS OGP_StudentID,

	 vSkillsStudentOnlyAnswer_AvgPivot.Period01 AS CrossCollPeriod01,  vSkillsStudentOnlyAnswer_AvgPivot.Period02 AS CrossCollPeriod02,  vSkillsStudentOnlyAnswer_AvgPivot.Period03 AS CrossCollPeriod03, vSkillsStudentOnlyAnswer_AvgPivot.Period04 AS CrossCollPeriod04,  vSkillsStudentOnlyAnswer_AvgPivot.Period05 AS CrossCollPeriod05,  
	 vSkillsStudentOnlyAnswer_AvgPivot.Period06 AS CrossCollPeriod06,  vSkillsStudentOnlyAnswer_AvgPivot.Period07 AS CrossCollPeriod07,  vSkillsStudentOnlyAnswer_AvgPivot.Period08 AS CrossCollPeriod08, vSkillsStudentOnlyAnswer_AvgPivot.Period09 AS CrossCollPeriod09,  vSkillsStudentOnlyAnswer_AvgPivot.Period10 AS CrossCollPeriod10,
	#TempvSkillsStudentAvg_Latest.Diff_Start_Latest AS CrossCollDifference,
	#TempvSkillsStudentAvg_Latest.StartingPeriod AS CrossCollStartingPeriod,
	#TempvSkillsStudentAvg_Latest.MostRecentPeriod AS CrossCollMostRecentPeriod,
	
	vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period01 AS AllCoursesPeriod01,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period02 AS AllCoursesPeriod02,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period03 AS AllCoursesPeriod03, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period04 AS AllCoursesPeriod04, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period05 AS AllCoursesPeriod05,  
	vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period06 AS AllCoursesPeriod06,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period07 AS AllCoursesPeriod07,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period08 AS AllCoursesPeriod08, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period09 AS AllCoursesPeriod09, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period10 AS AllCoursesPeriod10,
	#TempvSkillsAllCoursesAvg_Latest.Diff_Start_Latest AS AllCoursesDifference, 
	#TempvSkillsAllCoursesAvg_Latest.StartingPeriod AS AllCoursesStartingPeriod,
	#TempvSkillsAllCoursesAvg_Latest.MostRecentPeriod AS AllCoursesMostRecentPeriod,

	 vSkillsOverallStudentAnswer_AvgPivot.Period01 AS OverallPeriod01,  vSkillsOverallStudentAnswer_AvgPivot.Period02 AS OverallPeriod02,  vSkillsOverallStudentAnswer_AvgPivot.Period03 AS OverallPeriod03, vSkillsOverallStudentAnswer_AvgPivot.Period04 AS OverallPeriod04, vSkillsOverallStudentAnswer_AvgPivot.Period05 AS OverallPeriod05,  
	 vSkillsOverallStudentAnswer_AvgPivot.Period06 AS OverallPeriod06,  vSkillsOverallStudentAnswer_AvgPivot.Period07 AS OverallPeriod07,  vSkillsOverallStudentAnswer_AvgPivot.Period08 AS OverallPeriod08, vSkillsOverallStudentAnswer_AvgPivot.Period09 AS OverallPeriod09, vSkillsOverallStudentAnswer_AvgPivot.Period10 AS OverallPeriod10,
	#TempvSkillsOverallAvg_Latest.Diff_Start_Latest AS OverallDifference, 
	#TempvSkillsOverallAvg_Latest.StartingPeriod AS OverallStartingPeriod,
	#TempvSkillsOverallAvg_Latest.MostRecentPeriod AS OverallMostRecentPeriod

FROM 
	OGP_Student 
	LEFT JOIN #TempvSkillsStudentAvg_Latest ON OGP_Student.ID =  #TempvSkillsStudentAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsStudentOnlyAnswer_AvgPivot ON OGP_Student.ID = vSkillsStudentOnlyAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsOverallAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsOverallStudentAnswer_AvgPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsAllCoursesAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot ON OGP_Student.ID = vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.OGP_StudentID

	LEFT JOIN vSkillsOverallStudentAnswer_CountPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_CountPivot.OGP_StudentID
	LEFT JOIN #TempOverallStudentCompletion ON OGP_Student.ID = #TempOverallStudentCompletion.OGP_StudentID

WHERE
	OGP_Student.AcademicYearID = @AcademicYearID
	AND OGP_Student.RecordStatus <> 'Obsolete'

GO





SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [sp_WebPlus_Skills_Reporting_ByStudent] '23/24'

ALTER PROCEDURE [sp_WebPlus_Skills_Reporting_ByStudent]
  @AcademicYearID   AS VARCHAR(5)
AS
--AMG 13/02/2024
--The DataSource for the main reporting page in OneGradePlus for Skills
--We need to calculate the starting point and the MostRecentPoint
--So if a student starts in period 3, that is their starting point - we can then calculate the difference between the start and end point.
--Shows the Course Answers for the Course and the Overall Student Answers.
--AMG 25/03/2024
--This now only shows one row per student.
--It shows the CrossCollege Avg, the Avg for ALL Courses and the Overall Avg for each period, the Start and End and Difference also.
--AMG 06/04/2024
--Removed unnecessary course / enrolment related NULL fields.
--DECLARE @AcademicYearID   AS VARCHAR(5)
--SELECT @AcademicYearID = '23/24'
----Now, (for Student Cross College) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN Period02 IS NULL THEN
			(CASE WHEN Period03 IS NULL THEN
				(CASE WHEN Period04 IS NULL THEN
					(CASE WHEN Period05 IS NULL THEN
						(CASE WHEN Period06 IS NULL THEN
							(CASE WHEN Period07 IS NULL THEN
								(CASE WHEN Period08 IS NULL THEN
									(CASE WHEN Period09 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period09 END)
								ELSE Period08 END)
							ELSE Period07 END) 
						ELSE Period06 END)
					ELSE Period05 END)
				ELSE Period04 END)
			ELSE Period03 END)
		ELSE Period02 END)
	ELSE Period01 END)

AS StartingPeriodValue,

	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)

AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsStudentOnlyAnswerAvg_Latestsub
FROM vSkillsStudentOnlyAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID


--Now, (for ALL Courses) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
AcademicYearID,

	(CASE WHEN  Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE  Period10 END)
									ELSE  Period09 END)
								ELSE  Period08 END)
							ELSE  Period07 END) 
						ELSE  Period06 END)
					ELSE  Period05 END)
				ELSE  Period04 END)
			ELSE  Period03 END)
		ELSE  Period02 END)				
	ELSE  Period01 END)
 AS StartingValue,

	(CASE WHEN  Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN 
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod, 
 
  	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
 					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 10 END)
AS MostRecentPeriod
INTO #TempvSkillsCourseStudentOnlyAnswerAvg_AllCourses_Latestsub
FROM vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot
WHERE AcademicYearID = @AcademicYearID

--Now, (for Overall) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN Period02 IS NULL THEN
			(CASE WHEN Period03 IS NULL THEN
				(CASE WHEN Period04 IS NULL THEN
					(CASE WHEN Period05 IS NULL THEN
						(CASE WHEN Period06 IS NULL THEN
							(CASE WHEN Period07 IS NULL THEN
								(CASE WHEN Period08 IS NULL THEN
									(CASE WHEN Period09 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period09 END)
								ELSE Period08 END)
							ELSE Period07 END) 
						ELSE Period06 END)
					ELSE Period05 END)
				ELSE Period04 END)
			ELSE Period03 END)
		ELSE Period02 END)
	ELSE Period01 END)
AS StartingPeriodValue,

	(CASE WHEN Period01 IS NULL THEN
		(CASE WHEN  Period02 IS NULL THEN
			(CASE WHEN  Period03 IS NULL THEN
				(CASE WHEN  Period04 IS NULL THEN
					(CASE WHEN  Period05 IS NULL THEN
						(CASE WHEN  Period06 IS NULL THEN
							(CASE WHEN  Period07 IS NULL THEN
								(CASE WHEN  Period08 IS NULL THEN
									(CASE WHEN  Period09 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE  Period01 END)
									ELSE  Period02 END)
								ELSE  Period03 END)
							ELSE  Period04 END) 
						ELSE  Period05 END)
					ELSE  Period06 END)
				ELSE  Period07 END)
			ELSE  Period08 END)
		ELSE  Period09 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period09 IS NULL THEN
			(CASE WHEN  Period08 IS NULL THEN
				(CASE WHEN  Period07 IS NULL THEN
					(CASE WHEN  Period06 IS NULL THEN
						(CASE WHEN  Period05 IS NULL THEN
							(CASE WHEN  Period04 IS NULL THEN
								(CASE WHEN  Period03 IS NULL THEN
									(CASE WHEN  Period02 IS NULL THEN
										(CASE WHEN  Period01 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsOverallStudentAnswerAvg_Latestsub
FROM vSkillsOverallStudentAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID


--and (for Student Cross-College) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsStudentAvg_Latest
FROM
	#TempvSkillsStudentOnlyAnswerAvg_Latestsub

--and (for ALL Courses) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	AcademicYearID,
	StartingPeriod,
	MostRecentPeriod,
	(CASE WHEN StartingValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsAllCoursesAvg_Latest
FROM
	#TempvSkillsCourseStudentOnlyAnswerAvg_AllCourses_Latestsub

--and (for Overall) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsOverallAvg_Latest
FROM
	#TempvSkillsOverallStudentAnswerAvg_Latestsub


--For the Overall Student Enrolment Status (Cross College),
		
--The Enrolment OverallCompletionID should only be based on 'Imported' records.
--Firstly Load up a series of #Temptables with a count of the completion statuses ...
--It is important that we only take the 'Imported' records - we don't want to look at adhoc records here!
SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp1 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 1 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp2 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 2 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp3 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 3 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp4 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 4 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp5 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 5 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID

SELECT OGP_Enrolment.OGP_StudentID, Count(OGP_Enrolment.OverallCompletionID) AS CountCompletion INTO #TempStudent_Comp6 FROM OGP_Enrolment 
WHERE OGP_Enrolment.OverallCompletionID = 6 AND OGP_Enrolment.RecordStatus = 'Imported' AND OGP_Enrolment.AcademicYearID = @AcademicYearID GROUP BY OGP_Enrolment.OGP_StudentID



SELECT 
	OGP_Student.ID AS OGP_StudentID,
	(CASE WHEN CountCompletionID_1 > 0 THEN 1 ELSE --cont
		(CASE WHEN CountCompletionID_2 > 0 THEN 2 ELSE --complete
			(CASE WHEN CountCompletionID_3 > 0 THEN 3 ELSE --w/drawn
				(CASE WHEN CountCompletionID_6 > 0 THEN 6 ELSE --temp w/drawn
					(CASE WHEN CountCompletionID_4 > 0 THEN 4 ELSE --Xfer
					99 --Other!
					END) -- Xfer
				END)
			END)
		END)
	END) AS StudentOverallCompletionID

INTO
	#TempOverallStudentCompletion
FROM
	OGP_Student
		INNER JOIN
		(
		SELECT 
			OGP_Student.ID,
			(SELECT CountCompletion FROM #TempStudent_Comp1 WHERE #TempStudent_Comp1.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_1,
			(SELECT CountCompletion FROM #TempStudent_Comp2 WHERE #TempStudent_Comp2.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_2,
			(SELECT CountCompletion FROM #TempStudent_Comp3 WHERE #TempStudent_Comp3.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_3,
			(SELECT CountCompletion FROM #TempStudent_Comp4 WHERE #TempStudent_Comp4.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_4,
			(SELECT CountCompletion FROM #TempStudent_Comp5 WHERE #TempStudent_Comp5.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_5,
			(SELECT CountCompletion FROM #TempStudent_Comp6 WHERE #TempStudent_Comp6.OGP_StudentID = OGP_Student.ID) AS CountCompletionID_6
		FROM
			OGP_Student	
		WHERE
			OGP_Student.AcademicYearID = @AcademicYearID
			AND OGP_Student.RecordStatus <> 'Obsolete'
		) t

		ON t.ID = OGP_Student.ID
		 

	


SELECT

	OGP_Student.AcademicYearID, StudentRef, Forenames, Surname, 

	(SELECT dbo.GetAgeOnDate(
	CAST((SELECT CAST(AcademicYearNo AS VARCHAR(4)) FROM AcademicYear WHERE AcademicYear.AcademicYearID = OGP_Student.AcademicYearID ) + '0831' AS DateTime),
	OGP_Student.DOB
	)) AS AgeOn31Aug,
	Gender, 
	(SELECT Ethnicity.Code + ' - ' + Ethnicity.[Definition] FROM Ethnicity WHERE Ethnicity.ID = OGP_Student.EthnicityID) AS Ethnicity, 
	DOB, 

	#TempOverallStudentCompletion.StudentOverallCompletionID, 
	ISNULL((SELECT [Description] FROM Completion WHERE Completion.ID = #TempOverallStudentCompletion.StudentOverallCompletionID), 'n/a') AS CompletionStatus, 

	UserDefinedString1 AS StudentUserDefinedString1, UserDefinedString2 AS StudentUserDefinedString2, UserDefinedString3 AS StudentUserDefinedString3, UserDefinedString4 AS StudentUserDefinedString4,
	UserDefinedString5 AS StudentUserDefinedString5, UserDefinedString6 AS StudentUserDefinedString6, UserDefinedString7 AS StudentUserDefinedString7, UserDefinedString8 AS StudentUserDefinedString8,
	--UserDefinedNumber, UserDefinedNumber2, UserDefinedDate, 

	(SELECT LLDDandHealthProblem.Code + ' - ' + LLDDandHealthProblem.[Definition] FROM LLDDandHealthProblem WHERE LLDDandHealthProblem.ID = OGP_Student.LLDDandHealthProblemID) AS LLDDandHealthProblem, 
	OGP_Student.FreeSchoolMeal,
	
	OGP_Student.ID AS OGP_StudentID,

	 vSkillsStudentOnlyAnswer_AvgPivot.Period01 AS CrossCollPeriod01,  vSkillsStudentOnlyAnswer_AvgPivot.Period02 AS CrossCollPeriod02,  vSkillsStudentOnlyAnswer_AvgPivot.Period03 AS CrossCollPeriod03, vSkillsStudentOnlyAnswer_AvgPivot.Period04 AS CrossCollPeriod04,  vSkillsStudentOnlyAnswer_AvgPivot.Period05 AS CrossCollPeriod05,  
	 vSkillsStudentOnlyAnswer_AvgPivot.Period06 AS CrossCollPeriod06,  vSkillsStudentOnlyAnswer_AvgPivot.Period07 AS CrossCollPeriod07,  vSkillsStudentOnlyAnswer_AvgPivot.Period08 AS CrossCollPeriod08, vSkillsStudentOnlyAnswer_AvgPivot.Period09 AS CrossCollPeriod09,  vSkillsStudentOnlyAnswer_AvgPivot.Period10 AS CrossCollPeriod10,
	#TempvSkillsStudentAvg_Latest.Diff_Start_Latest AS CrossCollDifference,
	#TempvSkillsStudentAvg_Latest.StartingPeriod AS CrossCollStartingPeriod,
	#TempvSkillsStudentAvg_Latest.MostRecentPeriod AS CrossCollMostRecentPeriod,
	
	vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period01 AS AllCoursesPeriod01,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period02 AS AllCoursesPeriod02,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period03 AS AllCoursesPeriod03, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period04 AS AllCoursesPeriod04, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period05 AS AllCoursesPeriod05,  
	vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period06 AS AllCoursesPeriod06,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period07 AS AllCoursesPeriod07,  vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period08 AS AllCoursesPeriod08, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period09 AS AllCoursesPeriod09, vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.Period10 AS AllCoursesPeriod10,
	#TempvSkillsAllCoursesAvg_Latest.Diff_Start_Latest AS AllCoursesDifference, 
	#TempvSkillsAllCoursesAvg_Latest.StartingPeriod AS AllCoursesStartingPeriod,
	#TempvSkillsAllCoursesAvg_Latest.MostRecentPeriod AS AllCoursesMostRecentPeriod,

	 vSkillsOverallStudentAnswer_AvgPivot.Period01 AS OverallPeriod01,  vSkillsOverallStudentAnswer_AvgPivot.Period02 AS OverallPeriod02,  vSkillsOverallStudentAnswer_AvgPivot.Period03 AS OverallPeriod03, vSkillsOverallStudentAnswer_AvgPivot.Period04 AS OverallPeriod04, vSkillsOverallStudentAnswer_AvgPivot.Period05 AS OverallPeriod05,  
	 vSkillsOverallStudentAnswer_AvgPivot.Period06 AS OverallPeriod06,  vSkillsOverallStudentAnswer_AvgPivot.Period07 AS OverallPeriod07,  vSkillsOverallStudentAnswer_AvgPivot.Period08 AS OverallPeriod08, vSkillsOverallStudentAnswer_AvgPivot.Period09 AS OverallPeriod09, vSkillsOverallStudentAnswer_AvgPivot.Period10 AS OverallPeriod10,
	#TempvSkillsOverallAvg_Latest.Diff_Start_Latest AS OverallDifference, 
	#TempvSkillsOverallAvg_Latest.StartingPeriod AS OverallStartingPeriod,
	#TempvSkillsOverallAvg_Latest.MostRecentPeriod AS OverallMostRecentPeriod

FROM 
	OGP_Student 
	LEFT JOIN #TempvSkillsStudentAvg_Latest ON OGP_Student.ID =  #TempvSkillsStudentAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsStudentOnlyAnswer_AvgPivot ON OGP_Student.ID = vSkillsStudentOnlyAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsOverallAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsOverallStudentAnswer_AvgPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsAllCoursesAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot ON OGP_Student.ID = vSkillsCourseStudentOnlyAnswer_AllCourses_AvgPivot.OGP_StudentID

	LEFT JOIN vSkillsOverallStudentAnswer_CountPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_CountPivot.OGP_StudentID
	LEFT JOIN #TempOverallStudentCompletion ON OGP_Student.ID = #TempOverallStudentCompletion.OGP_StudentID

WHERE
	OGP_Student.AcademicYearID = @AcademicYearID
	AND OGP_Student.RecordStatus <> 'Obsolete'





GO
